package com.hz.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.hz.util.DateControl;
import com.hz.util.Pagination;
import com.hz.util.ToPinyin;
import com.hz.model.User;
import com.hz.dbconn.DBConnect;

public class UserControl {
    
    public static Pagination pageInstance = null;
	
	public static boolean insertUser(User user){
		DBConnect dbc = new DBConnect();
		String sql = "insert into user(username,password,stuNum,realname,sex,tel,qq,email,stuLevel,departmentId," +
		"majorId,grade,birthday,birthPlace,hobby,award,volunteerExp,volunteerNo," +
		"idCard,role,realnamePy,joinDate)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try{
		       int index = 1;
		       dbc.setString(index++, user.getUsername());
		       dbc.setString(index++, user.getPassword());
		       dbc.setString(index++,user.getStuNum());
		       dbc.setString(index++,user.getRealname());
		       dbc.setInt(index++,user.getSex());
		       dbc.setString(index++,user.getTel());
		       dbc.setString(index++,user.getQq());
		       dbc.setString(index++,user.getEmail());
		       dbc.setInt(index++,user.getStuLevel());
		       dbc.setInt(index++,user.getDepartmentId());
		       dbc.setInt(index++,user.getMajorId());
		       dbc.setInt(index++,user.getGrade());
		       dbc.setString(index++,user.getBirthday());
		       dbc.setString(index++,user.getBirthPlace());
		       dbc.setString(index++,user.getHobby());
		       dbc.setString(index++,user.getAward());
		       dbc.setString(index++,user.getVolunteerExp());
		       dbc.setString(index++,user.getVolunteerNo());
		       //dbc.setInt(index++,user.getVolunteerLev());
		       dbc.setString(index++,user.getIdCard());
		       dbc.setInt(index++,user.getRole());
		       //dbc.setString(index++,user.getPicPath());
		       dbc.setString(index++,ToPinyin.getPinYin(user.getRealname()));
		       dbc.setString(index++, DateControl.Date());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询数据库，插入新成员失败!");
			return false;
		}finally{
			dbc.close();
		}
	}
    
    
    
     //判断是否为用户,是的话返回其id，不是则返回0
      public  static int isUser(String username,String password){
	        DBConnect dbc = new DBConnect();
	        String sql = "SELECT id FROM user WHERE (username=? or stuNum=?) AND password=?";
	        int uid = 0;
	        dbc.prepareStatement(sql);
	        try{
	        	dbc.setString(1, username);
	        	dbc.setString(2, username);
	        	dbc.setString(3, password);
		        ResultSet rs = dbc.executeQuery();
		        if(rs.next()){
		            uid = rs.getInt("id");
			    return uid;
		       }else
			    return 0;
	           }catch (SQLException e){
		    e.printStackTrace();
	           }finally{
		       dbc.close();
	           }
	           System.out.println(uid);
	              return 0;
	              
                  }
      
      //判断学号是否已经存在
      public  static boolean isExists(String stunum){
	        DBConnect dbc = new DBConnect();
	        String sql = "SELECT stuNum FROM user WHERE stuNum = '"+stunum+"'";
	        dbc.prepareStatement(sql);
	        try{
		        ResultSet rs = dbc.executeQuery();
		        if(rs.next()){
		           return true;
		       }else
			    return false;
	           }catch (SQLException e){
		    e.printStackTrace();
	           }finally{
		       dbc.close();
	           }
	              return false;
           }
      //更新用户头像
      public  static boolean updateHeadImage(String picpath,int userId){
	        DBConnect dbc = new DBConnect();
	        String sql = "update user set picPath='"+picpath+"' where id='"+userId+"'";
	        dbc.prepareStatement(sql);
	        try{
		          dbc.executeUpdate();
		           return true;
		       
	           }catch (SQLException e){
		          e.printStackTrace();
		          
	           }finally{
		       dbc.close();
	           }
	           return false;
           }
         
     
       
     //根据id获取个人信息
       public static  User getUserInformation(int id){
  	      DBConnect dbc = new DBConnect();
  	      String sql = "SELECT * FROM user WHERE id=?";
  	      dbc.prepareStatement(sql);
  	      try{
  		  dbc.setInt(1, id);
  		    User user=new User();
  	            ResultSet rs = dbc.executeQuery();
  	           if(rs.next())
  	            {
  	                 user.setId(id);
  		         user.setUsername(rs.getString("username"));
  		         user.setPassword(rs.getString("password"));
  		         user.setStuNum(rs.getString("stuNum"));
  		         user.setSex(rs.getInt("sex"));
  		         user.setGrade(rs.getInt("grade"));
  		         user.setRole(rs.getInt("role"));
  	   	         user.setRealname(rs.getString("realname"));
  	 	         user.setDepartmentId(rs.getInt("departmentId"));
  	 	         user.setAward(rs.getString("award"));
  	 	         user.setBirthday(rs.getString("birthday"));
  	 	         user.setBirthPlace(rs.getString("birthPlace"));
  	 	         user.setEmail(rs.getString("email"));
  	 	         user.setHobby(rs.getString("hobby"));
  	 	         user.setIdCard(rs.getString("idCard"));
  	 	         user.setMajorId(rs.getInt("majorId"));
  	 	         user.setPicPath(rs.getString("picPath"));
  	 	         user.setVolunteerNo(rs.getString("volunteerNo"));
  	 	         user.setVolunteerLev(rs.getInt("volunteerLev"));
  	 	         user.setVolunteerExp(rs.getString("volunteerExp"));
  		         user.setQq(rs.getString("qq"));
  		         user.setSex(rs.getInt("sex"));
  		         user.setRole(rs.getInt("role"));
  		         user.setTel(rs.getString("tel"));
  		         user.setJoinDate(rs.getString("joinDate"));
  	 	         return user;
  	             }
                } catch (SQLException e) {
  	             e.printStackTrace();
  	             return null;
                  } finally {
  	                    dbc.close();
                 }
                return null;
       }
       
       //删除用户
       public  static boolean deleteUser(int id){
	     DBConnect dbc = new DBConnect();
	     String sql = "DELETE FROM user WHERE id=?";
	     dbc.prepareStatement(sql);
	     try {
		   dbc.setInt(1, id);
		   dbc.executeUpdate();
		   return true;
	      } catch (SQLException e) {
		   e.printStackTrace();
		   return false;
	     } finally {
		dbc.close();
	  }
       }
       
      //一键设为管理员
       public static boolean setAdmin(int uid) {
	   DBConnect dbc = new DBConnect();
	   String sql = "update user set role = 1 where id='"+uid+"'";
	   dbc.prepareStatement(sql);
	   try {
	       dbc.executeUpdate();
	       return true;
	} catch (Exception e) {
	    // TODO: handle exception
	    e.printStackTrace();
	    System.out.println("设为管理员失败");
	    return false;
	}finally{
	    dbc.close();
	    }
	
    }
       
       //修改用户
       public static  boolean updateUserBool(User user,int id){
            	DBConnect dbc = new DBConnect();
	        String sql = "UPDATE user SET stuNum=?,realname=?,sex=?,tel=?,qq=?,email=?,stuLevel=?,departmentId=?," +
	        		"majorId=?,grade=?,birthday=?,birthPlace=?,hobby=?,award=?,volunteerExp=?," +
	        		"idCard=?,realnamePy=? WHERE id=?";
	        dbc.prepareStatement(sql);
	        try {  
	               int index = 1;
		       //dbc.setString(index++, user.getUsername());
		      // dbc.setString(index++, user.getPassword());
		       dbc.setString(index++,user.getStuNum());
		       dbc.setString(index++,user.getRealname());
		       dbc.setInt(index++,user.getSex());
		       dbc.setString(index++,user.getTel());
		       dbc.setString(index++,user.getQq());
		       dbc.setString(index++,user.getEmail());
		       dbc.setInt(index++,user.getStuLevel());
		       dbc.setInt(index++,user.getDepartmentId());
		       dbc.setInt(index++,user.getMajorId());
		       dbc.setInt(index++,user.getGrade());
		       dbc.setString(index++,user.getBirthday());
		       dbc.setString(index++,user.getBirthPlace());
		       dbc.setString(index++,user.getHobby());
		       dbc.setString(index++,user.getAward());
		       dbc.setString(index++,user.getVolunteerExp());
		       dbc.setString(index++,user.getIdCard());
		       //dbc.setString(index++,user.getPicPath());
		       dbc.setString(index++,ToPinyin.getPinYin(user.getRealname()));
		       dbc.setInt(index++, id);
		       dbc.executeUpdate();
		       return true;
	           } catch (SQLException e) {
		        e.printStackTrace();
		       return false;
	          } finally {
		       dbc.close();
	    }
       }
       
     //修改密码
       public static  boolean updatePassword(String pwd,int id){
            	DBConnect dbc = new DBConnect();
	        String sql = "UPDATE user SET password = '"+pwd+"' WHERE id='"+id+"'";
	        dbc.prepareStatement(sql);
	        try {  
	               
		       dbc.executeUpdate();
		       return true;
	           } catch (SQLException e) {
		        e.printStackTrace();
		       return false;
	          } finally {
		       dbc.close();
	    }
       }
       
       /**
        * 
        * @return
        */
       public static boolean LevelReset() {
	DBConnect dbConnect=  new DBConnect();
	boolean flag=false;
	String sql = "update user set volunteerLev = '0'";
	dbConnect.prepareStatement(sql);
	try {
	    //同时修改service表中所有inUsing值
	    if(dbConnect.executeUpdate()&&ServiceControl.ChageInUsing()){
		flag=true; 
	    }
	} catch (Exception e) {
	    // TODO: handle exception
	    e.printStackTrace();
	    System.out.println("RESETLEVL ERROR");
	}finally{
	    dbConnect.close();
	}
	return flag;
    }
       
       /**
        * 
        * @param name
        * @param stunum
        * @param departmentid
        * @param specialid
        * @param grade
        * @param stuLev
        * @param role
        * @param volNo
        * @param volLev
        * @param actId
        * @param hobby
        * @return
        * @throws SQLException
        * @throws Exception
        */
     //多重条件查询(不包含分页显示)
   	public static Collection<User> getAllUsersByMulti(String name, String stunum,String departmentid, String specialid, String grade,String stuLev, String role,String volNo,String volLev,String actId,String hobby) throws SQLException, Exception {
   	    String temp = " WHERE ";
   		String clause = temp;
   		String sql ="";

   		if (name != null&&!name.equals(""))
   			clause = clause + "realname like '%" + name + "%' ";
   		
   		if (!clause.equals(temp) && stunum != null&&!stunum.equals(""))
   			clause = clause + " and stunum like '%" + stunum + "%' ";

   		if (clause.equals(temp) && stunum != null&&!stunum.equals(""))
   			clause = clause + "stunum like '%" + stunum + "%' ";

   		if (!clause.equals(temp) && departmentid != null&&!departmentid.equals(""))
   			clause = clause + " and departmentId = '" + departmentid + "' ";

   		if (clause.equals(temp) && departmentid != null&&!departmentid.equals(""))
   			clause = clause + "departmentId = '" + departmentid + "' ";

   		if (!clause.equals(temp) && specialid != null&&!specialid.equals(""))
   			clause = clause + " and majorId = '" + specialid + "'";

   		if (clause.equals(temp) && specialid != null&&!specialid.equals(""))
   			clause = clause + "majorId = '" + specialid + "' ";

   		if (!clause.equals(temp) && grade != null&&!grade.equals(""))
   			clause = clause + " and grade like'%" + grade + "%' ";
   		
   		if (clause.equals(temp) && grade != null&&!grade.equals(""))
   			clause = clause + "grade like '%" + grade + "%' ";
   		
   		if (!clause.equals(temp) && role != null&&!role.equals(""))
   			clause = clause + " and role ='" + role + "' ";
   		if (clause.equals(temp) && role != null&&!role.equals(""))
   			clause = clause + " role = '" + role + "' ";
   		
   		if (!clause.equals(temp) && stuLev != null&&!stuLev.equals(""))
   			clause = clause + " and stuLevel ='" + stuLev + "' ";
   		if (clause.equals(temp) && stuLev != null&&!stuLev.equals(""))
   			clause = clause + " stuLevel = '" + stuLev + "' ";
   		
   		if (!clause.equals(temp) && hobby != null&&!hobby.equals(""))
   			clause = clause + " and hobby like'%" +"@"+ hobby+"@" + "%' ";
   		if (clause.equals(temp) && hobby != null&&!hobby.equals(""))
   			clause = clause + " hobby like '%" +"@"+ hobby+"@" + "%' ";
   		
   		if (!clause.equals(temp) && volNo != null&&!volNo.equals(""))
   			clause = clause + " and volunteerNo = '" + volNo + "' ";
   		if (clause.equals(temp) && volNo != null&&!volNo.equals(""))
   			clause = clause + " volunteerNo = '" + volNo + "' ";
   		
   		if (!clause.equals(temp) && volLev != null&&!volLev.equals(""))
   			clause = clause + " and volunteerLev = '" + volLev + "' ";
   		if (clause.equals(temp) && volLev != null&&!volLev.equals(""))
   			clause = clause + " volunteerLev = '" + volLev + "' ";
   		
   		
   		if (!clause.equals(temp) && actId != null&&!actId.equals(""))
   			clause = clause + " and service.actId = '"+actId+"'";
   		if (clause.equals(temp) && actId != null&&!actId.equals(""))
   			clause = clause + " service.actId = '"+actId+"' ";

   		if (clause.equals(temp)) {
   			clause = " ";
   		}
   		
   		if((actId!=null&&!actId.equals(""))){
   		    sql = "select user.* from user,service "+clause+" and service.userId=user.id";
   		     }
   		else{
   		    sql = "select * from user " + clause ;
   		}
   		Collection<User> userList = new ArrayList<User>();
   		DBConnect dbConnect = new DBConnect();
   		dbConnect.prepareStatement(sql);
		try {
			ResultSet rs = dbConnect.executeQuery();
			while (rs.next()) {
			         User user = new User();
			         user.setId(rs.getInt("id"));
	  		         user.setUsername(rs.getString("username"));
	  		         user.setPassword(rs.getString("password"));
	  		         user.setStuNum(rs.getString("stuNum"));
	  		         user.setSex(rs.getInt("sex"));
	  		         user.setGrade(rs.getInt("grade"));
	  	   	         user.setRealname(rs.getString("realname"));
	  	 	         user.setDepartmentId(rs.getInt("departmentId"));
	  	 	         user.setAward(rs.getString("award"));
	  	 	         user.setBirthday(rs.getString("birthday"));
	  	 	         user.setBirthPlace(rs.getString("birthPlace"));
	  	 	         user.setEmail(rs.getString("email"));
	  	 	         user.setHobby(rs.getString("hobby"));
	  	 	         user.setIdCard(rs.getString("idCard"));
	  	 	         user.setMajorId(rs.getInt("majorId"));
	  	 	         user.setPicPath(rs.getString("picPath"));
	  	 	         user.setVolunteerNo(rs.getString("volunteerNo"));
	  	 	         user.setVolunteerLev(rs.getInt("volunteerLev"));
	  	 	         user.setVolunteerExp(rs.getString("volunteerExp"));
	  		         user.setQq(rs.getString("qq"));
	  		         user.setSex(rs.getInt("sex"));
	  		         user.setRole(rs.getInt("role"));
	  		         user.setTel(rs.getString("tel"));
	  		         user.setJoinDate(rs.getString("joinDate"));
				     userList.add(user);
				     user = null;
			} 

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return userList;
   	}
          
       //多重条件查询(分页显示)
	public static Collection<Object> getUsersByMulti(String name, String stunum,String departmentid, String specialid, String grade,String stuLev, String role,String volNo,String volLev,String actId,String hobby,int page, int rowsPerPage) throws SQLException, Exception {
	        String temp = " WHERE ";
		String clause = temp;
		String sql ="";

		if (name != null&&!name.equals(""))
			clause = clause + "realname like '%" + name + "%' ";
		
		if (!clause.equals(temp) && stunum != null&&!stunum.equals(""))
			clause = clause + " and stunum like '%" + stunum + "%' ";

		if (clause.equals(temp) && stunum != null&&!stunum.equals(""))
			clause = clause + "stunum like '%" + stunum + "%' ";

		if (!clause.equals(temp) && departmentid != null&&!departmentid.equals(""))
			clause = clause + " and departmentId = '" + departmentid + "' ";

		if (clause.equals(temp) && departmentid != null&&!departmentid.equals(""))
			clause = clause + "departmentId = '" + departmentid + "' ";

		if (!clause.equals(temp) && specialid != null&&!specialid.equals(""))
			clause = clause + " and majorId = '" + specialid + "'";

		if (clause.equals(temp) && specialid != null&&!specialid.equals(""))
			clause = clause + "majorId = '" + specialid + "' ";

		if (!clause.equals(temp) && grade != null&&!grade.equals(""))
			clause = clause + " and grade like'%" + grade + "%' ";
		
		if (clause.equals(temp) && grade != null&&!grade.equals(""))
			clause = clause + "grade like '%" + grade + "%' ";
		
		if (!clause.equals(temp) && role != null&&!role.equals(""))
			clause = clause + " and role ='" + role + "' ";
		if (clause.equals(temp) && role != null&&!role.equals(""))
			clause = clause + " role = '" + role + "' ";
		
		if (!clause.equals(temp) && stuLev != null&&!stuLev.equals(""))
			clause = clause + " and stuLevel ='" + stuLev + "' ";
		if (clause.equals(temp) && stuLev != null&&!stuLev.equals(""))
			clause = clause + " stuLevel = '" + stuLev + "' ";
		
		if (!clause.equals(temp) && hobby != null&&!hobby.equals(""))
			clause = clause + " and hobby like'%" +"@"+ hobby+"@" + "%' ";
		if (clause.equals(temp) && hobby != null&&!hobby.equals(""))
			clause = clause + " hobby like '%" +"@"+ hobby+"@" + "%' ";
		
		if (!clause.equals(temp) && volNo != null&&!volNo.equals(""))
			clause = clause + " and volunteerNo = '" + volNo + "' ";
		if (clause.equals(temp) && volNo != null&&!volNo.equals(""))
			clause = clause + " volunteerNo = '" + volNo + "' ";
		
		if (!clause.equals(temp) && volLev != null&&!volLev.equals(""))
			clause = clause + " and volunteerLev = '" + volLev + "' ";
		if (clause.equals(temp) && volLev != null&&!volLev.equals(""))
			clause = clause + " volunteerLev = '" + volLev + "' ";
		
		
		if (!clause.equals(temp) && actId != null&&!actId.equals(""))
			clause = clause + " and service.actId = '"+actId+"'";
		if (clause.equals(temp) && actId != null&&!actId.equals(""))
			clause = clause + " service.actId = '"+actId+"' ";

		if (clause.equals(temp)) {
			clause = " ";
		}
		
		if((actId!=null&&!actId.equals(""))){
		    sql = "select user.* from user,service "+clause+" and service.userId=user.id";
		     }
		else{
		    sql = "select * from user " + clause ;
		}
		System.out.println(sql);
		pageInstance = new UserControlPage(sql, rowsPerPage);
		try {
		     return pageInstance.getPage(page);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
       
}

class UserControlPage extends Pagination {
	
	public UserControlPage(String sql, int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}

	public Collection<User> packResultSet(ResultSet rs) {
		Collection<User> userList = new ArrayList<User>();
		try {
			while (rs.next()) {
			         User user = new User();
			         user.setId(rs.getInt("id"));
	  		         user.setUsername(rs.getString("username"));
	  		         user.setPassword(rs.getString("password"));
	  		         user.setStuNum(rs.getString("stuNum"));
	  		         user.setSex(rs.getInt("sex"));
	  		         user.setGrade(rs.getInt("grade"));
	  	   	         user.setRealname(rs.getString("realname"));
	  	 	         user.setDepartmentId(rs.getInt("departmentId"));
	  	 	         user.setAward(rs.getString("award"));
	  	 	         user.setBirthday(rs.getString("birthday"));
	  	 	         user.setBirthPlace(rs.getString("birthPlace"));
	  	 	         user.setEmail(rs.getString("email"));
	  	 	         user.setHobby(rs.getString("hobby"));
	  	 	         user.setIdCard(rs.getString("idCard"));
	  	 	         user.setMajorId(rs.getInt("majorId"));
	  	 	         user.setPicPath(rs.getString("picPath"));
	  	 	         user.setVolunteerNo(rs.getString("volunteerNo"));
	  	 	         user.setVolunteerLev(rs.getInt("volunteerLev"));
	  	 	         user.setVolunteerExp(rs.getString("volunteerExp"));
	  		         user.setQq(rs.getString("qq"));
	  		         user.setSex(rs.getInt("sex"));
	  		         user.setRole(rs.getInt("role"));
	  		         user.setTel(rs.getString("tel"));
	  		         user.setJoinDate(rs.getString("joinDate"));
				 userList.add(user);
				 user = null;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userList;
	}
       
       
}
